package com.qxsx.midi.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.qxsx.midi.model.MyMusicFile;

import java.util.ArrayList;
import java.util.List;


public class MyMusicFileDBTool {
    private SQLiteDatabase db;
    private Context context;

    public MyMusicFileDBTool(Context context) {
        this.context = context;
    }

    private void connDB() {
        if (null == db) {
            db = new MyMusicOpenHelper(context).getWritableDatabase();
        }
    }

    private void closeDB() {
        if (null != db) {
            db.close();
            db = null;
        }
    }


    public void insertOne(MyMusicFile file) {
        connDB();

        ContentValues values = new ContentValues();

        //values.put(MyMusicOpenHelper.COLUMN_ID , file.get_id()) ;
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_MAJOR, file.get_major());
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_GRADE, file.get_grade());
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_NAME, file.get_name());
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_CONTENT, file.get_content());
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_IMAGE, file.get_image());
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_MUSIC, file.get_music());
        db.insert(MyMusicOpenHelper.TABLE_NAME, null, values);

        closeDB();
    }

//	public void insertOne1(MyFile file) {
//		connDB() ;
//
//		String sql = "INSERT INTO " + MyMusicOpenHelper.TABLE_NAME +"("+MyMusicOpenHelper.COLUMN_MOVIE_TITLE+","+
//				MyMusicOpenHelper.COLUMN_MOVIE_SIZE+","+MyMusicOpenHelper.COLUMN_MOVIE_LASTMODIFIED+","+MyMusicOpenHelper.COLUMN_MOVIE_PATH
//				+","+MyMusicOpenHelper.COLUMN_MOVIE_IMAGE+","+MyMusicOpenHelper.COLUMN_MOVIE_THUMBNAIL+")" + "VALUES(?,?,?,?,?,?)";
//		db.beginTransaction();
//
//		SQLiteStatement stmt = db.compileStatement(sql);
////		ContentValues values = new ContentValues() ;
////		values.put(MyMusicOpenHelper.COLUMN_MOVIE_TITLE , file.getFileName()) ;
////		values.put(MyMusicOpenHelper.COLUMN_MOVIE_SIZE, file.getFileSize());
////		values.put(MyMusicOpenHelper.COLUMN_MOVIE_LASTMODIFIED, file.getFileTime());
////		values.put(MyMusicOpenHelper.COLUMN_MOVIE_PATH , file.getFilePath()) ;
////		values.put(MyMusicOpenHelper.COLUMN_MOVIE_IMAGE , file.getFileImage()) ;
////		values.put(MyMusicOpenHelper.COLUMN_MOVIE_THUMBNAIL , file.getIsHaveThumbnail()) ;
//
//		stmt.bindString(1, file.getFileName());
//		stmt.bindString(2, file.getFileSize());
//		stmt.bindString(3, file.getFileTime());
//		stmt.bindString(4, file.getFilePath());
//		stmt.bindBlob(5, file.getFileImage());
//		stmt.bindLong(6, file.getIsHaveThumbnail());
//
//
//		    stmt.execute();
//		    stmt.clearBindings();
//
//
//
////		db.insert(MyMusicOpenHelper.TABLE_NAME, null, values) ;
//
//		db.setTransactionSuccessful();
//		db.endTransaction();
//		closeDB() ;
//	}
//
//	public void insertOne2(List<MyFile> file) {
//		connDB() ;
//
//
//		String sql = "INSERT INTO " + MyMusicOpenHelper.TABLE_NAME +"("+MyMusicOpenHelper.COLUMN_MOVIE_TITLE+","+
//				MyMusicOpenHelper.COLUMN_MOVIE_SIZE+","+MyMusicOpenHelper.COLUMN_MOVIE_LASTMODIFIED+","+MyMusicOpenHelper.COLUMN_MOVIE_PATH
//				+","+MyMusicOpenHelper.COLUMN_MOVIE_IMAGE+","+MyMusicOpenHelper.COLUMN_MOVIE_THUMBNAIL+")" + "VALUES(?,?,?,?,?,?)";
//		db.beginTransaction();
//
////		for (int i = 0; i < file.size(); i++) {
////
////		}
//		SQLiteStatement stmt = db.compileStatement(sql);
//		for (MyFile file2:file) {
//
//			stmt.bindString(1, file2.getFileName());
//			stmt.bindString(2, file2.getFileSize());
//			stmt.bindString(3, file2.getFileTime());
//			stmt.bindString(4, file2.getFilePath());
//			stmt.bindBlob(5, file2.getFileImage());
//			stmt.bindLong(6, file2.getIsHaveThumbnail());
//		}
//
//
//
//
//
//		stmt.execute();
//		stmt.clearBindings();
//
//
//
////		db.insert(MyMusicOpenHelper.TABLE_NAME, null, values) ;
//
//		db.setTransactionSuccessful();
//		db.endTransaction();
//		closeDB() ;
//	}


    public List<MyMusicFile> getAll() {
        connDB();
        String sql = "SELECT * FROM " + MyMusicOpenHelper.TABLE_NAME;//+" ORDER BY " + MyMusicOpenHelper.COLUMN_MOVIE_TITLE;
        Cursor cursor = db.rawQuery(sql, null);
        List<MyMusicFile> data = new ArrayList<MyMusicFile>();
        while (cursor.moveToNext()) {
            MyMusicFile myFile = new MyMusicFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6));
            data.add(myFile);
        }
        cursor.close();
        closeDB();
        return data;
    }


    public MyMusicFile getOne(long id) {
        connDB();
        String sql = "SELECT * FROM " + MyMusicOpenHelper.TABLE_NAME + " WHERE " + MyMusicOpenHelper.COLUMN_ID + "=?";
        Cursor cursor = db.rawQuery(sql, new String[]{id + ""});
        MyMusicFile myFile = null;
        if (cursor.moveToNext()) {
            myFile = new MyMusicFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6));
        }
        closeDB();
        return myFile;
    }


    public List<MyMusicFile> getOne1(String major, String grade) {
        //boolean flag = false;
        connDB();
        List<MyMusicFile> data = new ArrayList<MyMusicFile>();
        if (major.equals("") && !grade.equals("")) {
            String sql = "SELECT * FROM " + MyMusicOpenHelper.TABLE_NAME + " WHERE " + MyMusicOpenHelper.COLUMN_MUSIC_GRADE + "=?";
            Cursor cursor = db.rawQuery(sql, new String[]{grade + ""});
            @SuppressWarnings("unused")
            MyMusicFile myFile = null;
            while (cursor.moveToNext()) {
                myFile = new MyMusicFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6));
                data.add(myFile);
            }
            closeDB();
        } else if (!major.equals("") && grade.equals("")) {
            String sql = "SELECT * FROM " + MyMusicOpenHelper.TABLE_NAME + " WHERE " + MyMusicOpenHelper.COLUMN_MUSIC_MAJOR + "=?";
            Cursor cursor = db.rawQuery(sql, new String[]{major + ""});
            @SuppressWarnings("unused")
            MyMusicFile myFile = null;
            while (cursor.moveToNext()) {
                myFile = new MyMusicFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6));
                data.add(myFile);
            }
            closeDB();
        } else if (!major.equals("") && !grade.equals("")) {
            String sql = "SELECT * FROM " + MyMusicOpenHelper.TABLE_NAME + " WHERE " + MyMusicOpenHelper.COLUMN_MUSIC_MAJOR + "=?" + " AND " + MyMusicOpenHelper.COLUMN_MUSIC_GRADE + "=?";
            Cursor cursor = db.rawQuery(sql, new String[]{major + "", grade + ""});
            @SuppressWarnings("unused")
            MyMusicFile myFile = null;
            while (cursor.moveToNext()) {
                myFile = new MyMusicFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6));
                data.add(myFile);
            }
            closeDB();
        }else if (major.equals("") && grade.equals("")) {
            connDB();
            String sql = "SELECT * FROM " + MyMusicOpenHelper.TABLE_NAME;//+" ORDER BY " + MyMusicOpenHelper.COLUMN_MOVIE_TITLE;
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                MyMusicFile myFile = new MyMusicFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6));
                data.add(myFile);
            }
            cursor.close();
            closeDB();
        }


        return data;

//
//        List<MyMusicFile> data = new ArrayList<MyMusicFile>();
//        @SuppressWarnings("unused")
//        MyMusicFile myFile = null;
//        if (cursor.moveToNext()) {
//            myFile = new MyMusicFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6));
//            data.add(myFile);
//        }
//        closeDB();

    }
//	public Boolean getOne1(String fileName) {
//		boolean flag = false;
//		connDB() ;
//		String sql = "SELECT * FROM " + MyMusicOpenHelper.TABLE_NAME + " WHERE " + MyMusicOpenHelper.COLUMN_MOVIE_TITLE + "=?" ;
//		Cursor cursor = db.rawQuery(sql, new String[]{fileName + ""}) ;
//		@SuppressWarnings("unused")
//		MyFile myFile = null ;
//		if(cursor.moveToNext()) {
//			myFile = new MyFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),cursor.getBlob(5),cursor.getInt(6)) ;
//			flag = true;
//		}
//		closeDB() ;
//		return flag ;
//	}

    public void deleteOne(long id) {
        connDB();
        String sql = "DELETE FROM " + MyMusicOpenHelper.TABLE_NAME + " WHERE " + MyMusicOpenHelper.COLUMN_ID + "=?";
        db.execSQL(sql, new Object[]{id});
        closeDB();
    }

    public void deleteAll() {
        connDB();
        String sql = "DELETE FROM " + MyMusicOpenHelper.TABLE_NAME;
        db.execSQL(sql);
        closeDB();
    }

    public void updateOne(long id, MyMusicFile file) {
        connDB();
        ContentValues values = new ContentValues();
        //values.put(MyMusicOpenHelper.COLUMN_ID , file.get_id()) ;
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_MAJOR, file.get_major());
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_GRADE, file.get_grade());
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_NAME, file.get_name());
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_CONTENT, file.get_content());
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_IMAGE, file.get_image());
        values.put(MyMusicOpenHelper.COLUMN_MUSIC_MUSIC, file.get_music());
        db.update(MyMusicOpenHelper.TABLE_NAME, values, MyMusicOpenHelper.COLUMN_ID + "=?", new String[]{id + ""});
        closeDB();
    }

}













